Rename Change Table
This transformation is only available when the Store Changes replication option is turned on.
If your transformation type is Rename Change Table, you can do the following:
- Rename Change Table to (string)
- Add a prefix or suffix
- Remove a prefix or suffix
- Replace a prefix or suffix with different characters
- Convert Change Table name to uppercase
- Convert Change Table name to lowercase
- Rename Change Table (expression)
- Globally renaming a Change Table will override the Change Table suffix defined in the task settings.
- The Change Table name must be different from the source table names. Otherwise, a table error will occur.
Rename Change Table to (string)
Use the Rename Change Table to: [string] option to change the name of all Change Tables that you defined in the Transformation scope step to a different name. For example, if you have a Change Table called EMPLOYEE and want to change all instances of this name to EMP then enter the string EMP. You can enter any string in this field.
Add a prefix or suffix
Use the Add a prefix or suffix option to add additional characters to the beginning or end of the Change Table name for all Change Tables that fit the definition you created in the Transformation scope step. For example, if the Change Table name is EMPLOYEES, you can add a suffix, such as TAR or _TAR to the Change Table name for all Change Tables with that name. In this case, the resulting Change Table name will be EMPLOYEESTAR or EMPLOYEES_TAR.
To globally add a prefix or suffix:
- Select Add <Prefix/Suffix> <String> to matching Change Table names.
- Click the word Prefix or Suffix and select one of these two from the list.
- Click [string] to activate the field.
- Type the characters you want as the prefix or suffix. If you want to include an underscore or other legal character to separate the prefix/suffix from the original name, you must add it as part of the character string.
- Click Finish to add the rule to the Global Rules list.
Remove a prefix or suffix
Use the Remove a prefix or suffix option to remove a string of characters from the beginning or end of a Change Table name for all Change Tables that fit the definition you created in the Transformation scope step.
For example, you can use this option to remove the letters _REV from the Change Table name for all Change Tables with the name EMPLOYEES. In this case the Change Table name in the target will be EMPLOYEES.
To globally remove a prefix or suffix:
- Select Remove <Prefix/Suffix> <String> from matching Change Table names.
- Click the word Prefix or Suffix and select one of these two from the list.
- Click [string] to activate the field.
- Type the characters you want to remove. If you want to remove an underscore or other legal character from the original name, you must add it as part of the character string.
- Click Finish to add the rule to the Global Rules list.
Replace a prefix or suffix with different characters
Use the Replace a prefix or suffix option to replace a string of characters with a different string of characters. You determine whether to replace the characters at the beginning or end of a Change Table name for all Change Tables that fit the definition you created in the Transformation scope step.
For example, you can use this option to replace the letters _ORIG with _REPL in the Change Table names for all Change Tables called EMPLOYEE_ORIG. In this case the Change Table name in the target will be EMPLOYEE_REPL.
To globally replace a prefix or suffix:
- Select Replace <Prefix/Suffix> <String> by <String> for all matching Change Table names.
- Click the word Prefix or Suffix and select one of these two from the list.
- Click the first [string] to activate the field.
- Type the characters from the existing (source) schema that you want to replace. If you want to include an underscore or other legal character from the original name in the string that you want to replace, you must add it as part of the character string.
- Click the second [sting] to activate the field.
- Type the characters you want to use in the target. These characters replace the original (source) characters in the target.
- Click Finish to add the rule to the Global Rules list.
Convert Change Table name to uppercase
Use the convert to uppercase option to convert a Change Table name to all upper case. For example:
- Table_cat, becomes TABLE_CAT
- Change Table_cat, becomes TABLE_CAT
- taBLe_Cat, becomes TABLE_CAT
To globally change the Change Table name to all uppercase:
- Select Convert Change Table name to uppercase.
- Click Finish to add the rule to the Global Rules list.
Convert Change Table name to lowercase
Use the convert to lowercase option to convert a Change Table name to all lower case. For example:
- Table_cat, becomes Change Table_cat
- TABLE_CAT, becomes Change Table_cat
- taBLe_Cat, becomes Change Table_cat
To globally change the Change Table name to all lowercase:
- Select Convert Change Table name to lowercase.
- Click Finish to add the rule to the Global Rules list.
Rename Change Table (expression)
Use the Rename Change Table to [expression] option to change the name of all Change Tables that fit the definition you created in the Transformation scope step. For example, if you have a Change Table called EMPLOYEE and want to change all instances of this name as defined in the previous step it to EMP.
To change the Change Table name:
- Select Rename Change Table to: [expression]
-
Click the button to the right of the Rename Change Table option to open the Expression Editor. For information on how to use the Expression Editor, see Using the Expression Builder. Then go to step 4.
or
Click [expression] to activate the field and continue with step 3.
-
Type an SQLite expression or a string (in quotes) to rename the table. For example:
- "New_Change_Table_Name"
-
’PREF_’||$AR_M_SOURCE_TABLE_NAME||’_SUFF’
You can use the following metadata in the SQLite expression:
- $AR_M_SOURCE_COLUMN_DATATYPE
- $AR_M_SOURCE_COLUMN_NAME
- $AR_M_SOURCE_SCHEMA
- $AR_M_SOURCE_TABLE_NAME